package com.kgc.dao;

import com.kgc.entity.Car;
import com.kgc.entity.UserCar;
import com.kgc.untils.PageUtils;
import org.apache.commons.lang3.StringUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class AdminCarDao extends BaseDao {
    //查找全部的车位
    public List<Car> selectCar(PageUtils pageUtils, String status) {
        List<Car> list = new ArrayList<>();
        try {
            String sql = "SELECT * FROM car ";
            if (StringUtils.isNotEmpty(status)) {
                sql += " where status like '%" + status + "%'";
            }
            sql += " limit ?,?";
            ResultSet rs = super.getExecuteQuery(sql, pageUtils.start(), pageUtils.getPageSize());
            while (rs.next()) {
                list.add(new Car(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4)));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return list;
    }

    public int totalCar(String name) {
        try {
            String sql = "select count(*) from  car ";
            if (StringUtils.isNotEmpty(name)) {
                sql += " where name like '%" + name + "%'";
            }
            ResultSet rs = super.getExecuteQuery(sql);
            while (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return 0;
    }

    public int addCar(Car car) {
        return super.getExecuteUpdate("INSERT INTO `car` VALUES (null, ?, ?, 0);"
                , car.getName(), car.getType());
    }

    public int deleteCar(String id) {
        return super.getExecuteUpdate("delete from car where id=?", id);
    }

    public int updateCar(Car car) {
        String sql = "UPDATE `car` SET `name` = ?, `type` = ? WHERE `id` = ?";
        return super.getExecuteUpdate(sql, car.getName(), car.getType(), car.getId());
    }

    public List<UserCar> selectUserCar(PageUtils pageUtils, Integer id) {
        List<UserCar> list = new ArrayList<>();
        try {
            String sql = "SELECT c.id,u.id,uc.inTime,uc.outTime,u.username FROM user u INNER JOIN user_car uc on u.id = uc.user_id INNER JOIN car c on uc.car_id =c.id WHERE c.id =? limit ?,?";
            ResultSet rs = super.getExecuteQuery(sql, id, pageUtils.start(), pageUtils.getPageSize());
            while (rs.next()) {
                list.add(new UserCar(rs.getInt(1), rs.getInt(2),rs.getTimestamp(3), rs.getTimestamp(4), rs.getString(5)));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return list;
    }

    public int totaluserCar(Integer id) {
        String sql = "SELECT count(*) FROM user u INNER JOIN user_car uc on u.id = uc.user_id INNER JOIN car c on uc.car_id =c.id WHERE c.id =? ";
        try {
            ResultSet rs = super.getExecuteQuery(sql, id);
            while (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return 0;
    }

    /**
     * 查询车位使用者
     *
     * @param userId
     * @return
     */
    public List<UserCar> selectUserByUserId(Integer userId) {
        List<UserCar> list = new ArrayList<>();
        try {
            String sql = "select uc.*,c.name from user_car  uc inner join car c on uc.car_id = c.id where uc.user_id = ? and uc.status = 1";
            ResultSet rs = super.getExecuteQuery(sql, userId);
            while (rs.next()) {
                list.add(new UserCar(rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getTimestamp(4), rs.getTimestamp(5), null, rs.getString(7)));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return list;
    }


    /**
     * 根据ID查询车位使用者
     *
     * @param id
     * @return carName List
     */
    public UserCar selectUserCarById(Integer id) {
        try {
            String sql = "select * from user_car   where id = ?";
            ResultSet rs = super.getExecuteQuery(sql, id);
            while (rs.next()) {
                return new UserCar(rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getTimestamp(4), rs.getTimestamp(5));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return null;
    }


    /**
     * 修改车位状态
     *
     * @param id
     * @return
     */
    public int updateCarStatus(Integer id, Integer status) {
        String sql = "UPDATE `car` SET `status` = ? WHERE `id` = ?";
        return super.getExecuteUpdate(sql, status, id);
    }

    /**
     * 修改状态
     *
     * @param id
     * @return
     */
    public int updateUser(int id) {
        String sql = "UPDATE `user_car` SET `outTime` = now(), `status` = 0 WHERE `id` = ?";
        return super.getExecuteUpdate(sql, id);
    }

    /**
     * 查找空闲车位列表
     *
     * @param type 地上 地下
     * @return
     */
    public List<Car> selectFreeCar(int type) {
        List<Car> list = new ArrayList<>();
        try {
            String sql = "select * from car where `status` = 0 and type = ?";
            ResultSet rs = super.getExecuteQuery(sql, type);
            while (rs.next()) {
                list.add(new Car(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4)));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.closeConnection();
        }
        return list;
    }

    /**
     * 用户分配车位
     *
     * @param userCar
     * @return
     */
    public int addAdminCar(UserCar userCar) {
        String sql = "INSERT INTO `user_car`( `user_id`, `car_id`, `inTime`, `outTime`, `status`) VALUES (?, ?, now(), null, 1)";
        return super.getExecuteUpdate(sql, userCar.getUserId(), userCar.getCarId());
    }
}

